//
//  ActivitySinglton.m
//  UI12豆瓣大项目
//
//  Created by 李奇 on 15/11/3.
//  Copyright © 2015年 蓝鸥科技. All rights reserved.
//

#import "ActivitySinglton.h"
#import <sqlite3.h>

@implementation ActivitySinglton

+(instancetype)shareDataBaseSingleton{
    static ActivitySinglton *dataBaseSingleton = nil;
    if(dataBaseSingleton == nil){
        dataBaseSingleton = [[ActivitySinglton alloc] init];
    }
    return dataBaseSingleton;
}

static sqlite3 *db = nil;

-(void)openDB{
    if(db != nil){
        NSLog(@"数据库已经打开");
        return;
    }
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *path = [array lastObject];
    NSString *sqlPath = [path stringByAppendingPathComponent:@"Activity.sqlite"];
    NSLog(@"-----%@",sqlPath);
    
    int result = sqlite3_open(sqlPath.UTF8String, &db);
    if(result == SQLITE_OK){
        NSLog(@"打开数据库成功");
    }
    else{
        NSLog(@"打开数据库失败");
    }
}


//  收藏表

-(void)createCollectionTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS collectionActivity(ID INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT,begin_time TEXT,end_time TEXT,address TEXT,category_name TEXT,wisher_count INTEGER,participant_count INTEGER,image TEXT,name TEXT,content TEXT)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"成功创建收藏表");
    }
    else{
        NSLog(@"创建收藏表失败");
    }
}

//删除数据
-(void)deleteCollection:(NSString *)title{
    
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM collectionActivity WHERE title = '%@' ", title];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
    
}



//插入对象
-(void)insertCollectionTable:(Activity *)activity{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO collectionActivity(title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content) VALUES('%@' , '%@' , '%@' , '%@' , '%@' , '%ld' , '%ld' , '%@' , '%@' , '%@')",activity.title,activity.begin_time,activity.end_time,activity.address,activity.category_name,activity.wisher_count,activity.participant_count,activity.image,activity.name,activity.content];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"插入收藏表成功");
    }
    else{
        NSLog(@"插入收藏表失败");
    }
    
}

//根据元素查询模型1
-(Activity *)selectCollectionWithTitle:(NSString *)title{
    Activity *activity = [[Activity alloc] init];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM collectionActivity WHERE title = '%@'",title];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if(result == SQLITE_OK){
        NSLog(@"查询收藏准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *begin_time = sqlite3_column_text(stmt, 2);
            const unsigned char *end_time = sqlite3_column_text(stmt, 3);
            const unsigned char *address = sqlite3_column_text(stmt, 4);
            const unsigned char *category_name = sqlite3_column_text(stmt, 5);
            int wisher_count = sqlite3_column_int(stmt, 6);
            int participant_count = sqlite3_column_int(stmt, 7);
            const unsigned char *image = sqlite3_column_text(stmt, 8);
            const unsigned char *name = sqlite3_column_text(stmt, 9);
            const unsigned char *content = sqlite3_column_text(stmt, 10);
            
            
            NSInteger numberInteger = number;
            NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
            NSString *begin_timeStr = [NSString stringWithUTF8String:(const char *)begin_time];
            NSString *end_timeStr = [NSString stringWithUTF8String:(const char *)end_time];
            NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
            NSString *category_nameStr = [NSString stringWithUTF8String:(const char *)category_name];
            NSInteger wisher_countInteger = wisher_count;
            NSInteger participant_countInteger = participant_count;
            NSString *imageStr = [NSString stringWithUTF8String:(const char *)image];
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *contentStr = [NSString stringWithUTF8String:(const char *)content];
            //  title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content
            activity.title = titleStr;
            activity.begin_time = begin_timeStr;
            activity.end_time = end_timeStr;
            activity.address = addressStr;
            activity.category_name = category_nameStr;
            activity.wisher_count = wisher_countInteger;
            activity.participant_count = participant_countInteger;
            activity.image = imageStr;
            activity.name = nameStr;
            activity.content = contentStr;
        }
    }
    else{
        NSLog(@"查询收藏准备失败");
    }
    
    
    return activity;
}
//根据name元素查找所有元素2
-(NSMutableArray *)selectCollectionWithName:(NSString *)name{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM collectionActivity WHERE name = '%@'",name];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if(result == SQLITE_OK){
        NSLog(@"查询收藏准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            Activity *activity = [[Activity alloc] init];

            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *begin_time = sqlite3_column_text(stmt, 2);
            const unsigned char *end_time = sqlite3_column_text(stmt, 3);
            const unsigned char *address = sqlite3_column_text(stmt, 4);
            const unsigned char *category_name = sqlite3_column_text(stmt, 5);
            int wisher_count = sqlite3_column_int(stmt, 6);
            int participant_count = sqlite3_column_int(stmt, 7);
            const unsigned char *image = sqlite3_column_text(stmt, 8);
            const unsigned char *name = sqlite3_column_text(stmt, 9);
            const unsigned char *content = sqlite3_column_text(stmt, 10);
            
            
            NSInteger numberInteger = number;
            NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
            NSString *begin_timeStr = [NSString stringWithUTF8String:(const char *)begin_time];
            NSString *end_timeStr = [NSString stringWithUTF8String:(const char *)end_time];
            NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
            NSString *category_nameStr = [NSString stringWithUTF8String:(const char *)category_name];
            NSInteger wisher_countInteger = wisher_count;
            NSInteger participant_countInteger = participant_count;
            NSString *imageStr = [NSString stringWithUTF8String:(const char *)image];
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *contentStr = [NSString stringWithUTF8String:(const char *)content];
            //  title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content
            activity.title = titleStr;
            activity.begin_time = begin_timeStr;
            activity.end_time = end_timeStr;
            activity.address = addressStr;
            activity.category_name = category_nameStr;
            activity.wisher_count = wisher_countInteger;
            activity.participant_count = participant_countInteger;
            activity.image = imageStr;
            activity.name = nameStr;
            activity.content = contentStr;
            [array addObject:activity];
        }
    }
    else{
        NSLog(@"查询收藏准备失败");
    }
    
    
    return array;
}




-(NSMutableArray *)selectAllCollection{
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = @"SELECT * FROM collectionActivity";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if(result == SQLITE_OK){
        NSLog(@"查询收藏准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *begin_time = sqlite3_column_text(stmt, 2);
            const unsigned char *end_time = sqlite3_column_text(stmt, 3);
            const unsigned char *address = sqlite3_column_text(stmt, 4);
            const unsigned char *category_name = sqlite3_column_text(stmt, 5);
            int wisher_count = sqlite3_column_int(stmt, 6);
            int participant_count = sqlite3_column_int(stmt, 7);
            const unsigned char *image = sqlite3_column_text(stmt, 8);
            const unsigned char *name = sqlite3_column_text(stmt, 9);
            const unsigned char *content = sqlite3_column_text(stmt, 10);
            
            
            NSInteger numberInteger = number;
            NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
            NSString *begin_timeStr = [NSString stringWithUTF8String:(const char *)begin_time];
            NSString *end_timeStr = [NSString stringWithUTF8String:(const char *)end_time];
            NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
            NSString *category_nameStr = [NSString stringWithUTF8String:(const char *)category_name];
            NSInteger wisher_countInteger = wisher_count;
            NSInteger participant_countInteger = participant_count;
            NSString *imageStr = [NSString stringWithUTF8String:(const char *)image];
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *contentStr = [NSString stringWithUTF8String:(const char *)content];
            //  title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content
            Activity *activity = [[Activity alloc] init];
            activity.title = titleStr;
            activity.begin_time = begin_timeStr;
            activity.end_time = end_timeStr;
            activity.address = addressStr;
            activity.category_name = category_nameStr;
            activity.wisher_count = wisher_countInteger;
            activity.participant_count = participant_countInteger;
            activity.image = imageStr;
            activity.name = nameStr;
            activity.content = contentStr;
            
            [array addObject:activity];
            
            [activity release];
        }
    }
    else{
        NSLog(@"查询收藏准备失败");
    }
    
    return array;
}

-(void)dropCollectionTable{
    NSString *sql = @"DROP TABLE collectionActivity";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"删除收藏表成功");
    }
    else{
        NSLog(@"删除收藏表失败");
    }
    
}




//缓存表
-(void)createActivityTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS activity(ID INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT,begin_time TEXT,end_time TEXT,address TEXT,category_name TEXT,wisher_count INTEGER,participant_count INTEGER,image TEXT,name TEXT,content TEXT)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"成功创建缓存表");
    }
    else{
        NSLog(@"创建缓存表失败");
    }
    
}

-(void)insertActivityTable:(Activity *)activity{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO activity(title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content) VALUES('%@' , '%@' , '%@' , '%@' , '%@' , '%ld' , '%ld' , '%@' , '%@' , '%@')",activity.title,activity.begin_time,activity.end_time,activity.address,activity.category_name,activity.wisher_count,activity.participant_count,activity.image,activity.name,activity.content];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"插入缓存表成功");
    }
    else{
        NSLog(@"插入缓存表失败");
    }
    
}

-(void)dropActivityTable{
    NSString *sql = @"DROP TABLE activity";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"删除缓存表成功");
    }
    else{
        NSLog(@"删除缓存表失败");
    }
    
}

-(NSMutableArray *)selectAllActivity{
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = @"SELECT * FROM activity";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if(result == SQLITE_OK){
        NSLog(@"查询缓存准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *begin_time = sqlite3_column_text(stmt, 2);
            const unsigned char *end_time = sqlite3_column_text(stmt, 3);
            const unsigned char *address = sqlite3_column_text(stmt, 4);
            const unsigned char *category_name = sqlite3_column_text(stmt, 5);
            int wisher_count = sqlite3_column_int(stmt, 6);
            int participant_count = sqlite3_column_int(stmt, 7);
            const unsigned char *image = sqlite3_column_text(stmt, 8);
            const unsigned char *name = sqlite3_column_text(stmt, 9);
            const unsigned char *content = sqlite3_column_text(stmt, 10);
            
            
            NSInteger numberInteger = number;
            NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
            NSString *begin_timeStr = [NSString stringWithUTF8String:(const char *)begin_time];
            NSString *end_timeStr = [NSString stringWithUTF8String:(const char *)end_time];
            NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
            NSString *category_nameStr = [NSString stringWithUTF8String:(const char *)category_name];
            NSInteger wisher_countInteger = wisher_count;
            NSInteger participant_countInteger = participant_count;
            NSString *imageStr = [NSString stringWithUTF8String:(const char *)image];
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *contentStr = [NSString stringWithUTF8String:(const char *)content];
            //  title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content
            Activity *activity = [[Activity alloc] init];
            activity.title = titleStr;
            activity.begin_time = begin_timeStr;
            activity.end_time = end_timeStr;
            activity.address = addressStr;
            activity.category_name = category_nameStr;
            activity.wisher_count = wisher_countInteger;
            activity.participant_count = participant_countInteger;
            activity.image = imageStr;
            activity.name = nameStr;
            activity.content = contentStr;
            
            [array addObject:activity];
            
            [activity release];
        }
    }
    else{
        NSLog(@"查询缓存准备失败");
    }
    
    return array;
    
}

-(Activity *)selectActivityWithTitle:(NSString *)title{
    Activity *activity = [[Activity alloc] init];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM activity WHERE title = '%@'",title];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if(result == SQLITE_OK){
        NSLog(@"查询缓存准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *begin_time = sqlite3_column_text(stmt, 2);
            const unsigned char *end_time = sqlite3_column_text(stmt, 3);
            const unsigned char *address = sqlite3_column_text(stmt, 4);
            const unsigned char *category_name = sqlite3_column_text(stmt, 5);
            int wisher_count = sqlite3_column_int(stmt, 6);
            int participant_count = sqlite3_column_int(stmt, 7);
            const unsigned char *image = sqlite3_column_text(stmt, 8);
            const unsigned char *name = sqlite3_column_text(stmt, 9);
            const unsigned char *content = sqlite3_column_text(stmt, 10);
            
            
            NSInteger numberInteger = number;
            NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
            NSString *begin_timeStr = [NSString stringWithUTF8String:(const char *)begin_time];
            NSString *end_timeStr = [NSString stringWithUTF8String:(const char *)end_time];
            NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
            NSString *category_nameStr = [NSString stringWithUTF8String:(const char *)category_name];
            NSInteger wisher_countInteger = wisher_count;
            NSInteger participant_countInteger = participant_count;
            NSString *imageStr = [NSString stringWithUTF8String:(const char *)image];
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *contentStr = [NSString stringWithUTF8String:(const char *)content];
            //  title,begin_time,end_time,address,category_name,wisher_count,participant_count,image,name,content
            activity.title = titleStr;
            activity.begin_time = begin_timeStr;
            activity.end_time = end_timeStr;
            activity.address = addressStr;
            activity.category_name = category_nameStr;
            activity.wisher_count = wisher_countInteger;
            activity.participant_count = participant_countInteger;
            activity.image = imageStr;
            activity.name = nameStr;
            activity.content = contentStr;
        }
    }
    else{
        NSLog(@"查询缓存准备失败");
    }
    return activity;
    
}

//用户
//创建表
-(void)createUserTable{
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS lanou0920(number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT , password TEXT)";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"建表成功");
    }
    
    
}
//插入一个用户
-(void)insertUser:(User *)user
{
    
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO lanou0920(name , password) VALUES('%@', '%@')", user.name, user.password];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}

//修改一个用户
-(void)updateUser:(User *)user name:(NSString *)name
{
    NSString *sql = [NSString stringWithFormat:@"UPDATE lanou0920 SET name = '%@', password = '%@' WHERE name = '%@' ",user.name, user.password, name];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"修改成功");
    }else{
        NSLog(@"修改失败" );
    }
}

//删除数据
-(void)deleteUser:(NSString *)name{
    
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM lanou0920 WHERE name = '%@' ", name];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
    
}

//查询所有学生
-(NSMutableArray *)selectUser{
    
    NSMutableArray *array = [NSMutableArray array];

    NSString *sql = @"SELECT * FROM lanou0920";
    //创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    //查询前的准备工作
    //参数3.限制sql语句长度,-1为不限制
    //prepare将数据库对象db,sql语句,跟随指针对象stmt关联到一起
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    }else{
        NSLog(@"查询失败");
    }
    //循环遍历每一行数据
    while (sqlite3_step(stmt) == SQLITE_ROW){
        
        
        //取出第一列
        int number = sqlite3_column_int(stmt, 0);//0表示第一列  (主键)
        //取出第二列
        const unsigned char *name = sqlite3_column_text(stmt, 1);//0表示第一列
        //取出第三列
        const unsigned char *password = sqlite3_column_text(stmt, 2);
        
        //上述都是C语言的
        
        //将C语言类型转成OC类型
        NSInteger num = number;
        NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
        NSString *passwordStr = [NSString stringWithUTF8String:(const char *)password];
        
        //创建学生对象
        User *user = [[User alloc] init];
        user.name = nameStr;
        user.password = passwordStr;
        user.number = num;  //可以不设置这个属性
        
        
        [array addObject:user];
    }
    return array;
}

//查询
-(User *)selectUserWithTitle:(NSString *)name{
    User *user = [[User alloc] init];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM lanou0920 WHERE name = '%@'",name];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if(result == SQLITE_OK){
        NSLog(@"查询用户准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW){
            
            
            //取出第一列
            int number = sqlite3_column_int(stmt, 0);//0表示第一列  (主键)
            //取出第二列
            const unsigned char *name = sqlite3_column_text(stmt, 1);//0表示第一列
            
            //取出第三列
            const unsigned char *password = sqlite3_column_text(stmt, 2);

            
            //将C语言类型转成OC类型
            NSInteger num = number;
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *passwordStr = [NSString stringWithUTF8String:(const char *)password];
            
            //创建学生对象
#warning 多开了一个空间
//            User *user = [[User alloc] init];
            user.name = nameStr;
            user.password = passwordStr;
            user.number = num;
        }
    }
    else{
        NSLog(@"查询用户准备失败");
    }
    return user;
}



-(void)dropUserTable{
    NSString *sql = @"DROP TABLE lanou0920";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else{
        NSLog(@"删除表失败");
    }
}


@end
